/*
CREATE TABLE SurvivorUsers
(
	UserId int not null identity(1,1),
	Username varchar(50) not null,
	UserPwd varchar(50) null,
	DisplayName varchar(50) not null,
	HasPaid bit not null default(0),
	IsAdmin bit not null default(0)
)
GO
*/

/*
DROP TABLE SurvivorTeams
DROP TABLE SurvivorPicks
DROP TABLE SurvivorRounds
*/

CREATE TABLE SurvivorTeams
(
	TeamId int not null identity(1,1) primary key,
	TeamName varchar(50),
	SurvivorYear smallint not null default(2009),
	Region varchar(20) not null,
	Seed tinyint not null,
	StillAlive bit not null default(0)
)
GO

CREATE TABLE SurvivorPicks
(
	PickId int not null identity(1,1) primary key,
	Username nvarchar(255) not null,
	TeamId int null,
	BracketRound smallint null,
	IsWinner bit null
)
GO

CREATE TABLE SurvivorRounds
(	
	RoundId int not null identity(1,1) primary key,
	SurvivorYear smallint not null default(2009),
	RoundDesc varchar(100) null,
	Deadline datetime null,
	NumOfPicks tinyint null
)
GO

if not exists (select * from syscolumns where name='DisplayName' and id in 
	(select id from sysobjects where name='aspnet_users' and xtype='u'))
begin
	alter table aspnet_users add DisplayName nvarchar(50)
end
go

if not exists (select * from syscolumns where name='PaidMoney' and id in 
	(select id from sysobjects where name='aspnet_users' and xtype='u'))
begin
	alter table aspnet_users add PaidMoney nvarchar(15)
end
go

if exists (select * from syscolumns where name='MembershipId' and id in 
	(select id from sysobjects where name='aspnet_membership' and xtype='u'))
begin
	alter table aspnet_membership drop column MembershipId
end
go

if not exists (select * from syscolumns where name='Email' and id in 
	(select id from sysobjects where name='aspnet_users' and xtype='u'))
begin
	alter table aspnet_users add Email nvarchar(256)
end
go

if exists (select * from sysobjects where name='t_imgtrd_aspnet_Users')
	DROP TRIGGER t_imgtrd_aspnet_Users
GO
CREATE TRIGGER [t_imgtrd_aspnet_Users] ON aspnet_Membership
   FOR INSERT, UPDATE
AS
BEGIN
	UPDATE aspnet_Users
	SET aspnet_Users.Email = b.Email
	FROM aspnet_Users a, inserted b
	WHERE a.UserId = b.UserId
END
GO

if exists (select * from sysobjects where name='t_imgtrd_DisplayName')
	DROP TRIGGER t_imgtrd_DisplayName
GO
CREATE TRIGGER [t_imgtrd_DisplayName] ON aspnet_Users
   FOR INSERT
AS
BEGIN
	UPDATE aspnet_Users
	SET aspnet_Users.DisplayName = b.UserName
	FROM aspnet_Users a, inserted b
	WHERE a.UserId = b.UserId
	AND a.DisplayName IS NULL
END
GO

if not exists (select * from syscolumns where name='Round1Result' and id in 
	(select id from sysobjects where name='SurvivorTeams' and xtype='u'))
begin
	alter table SurvivorTeams add Round1Result bit
end
go
if not exists (select * from syscolumns where name='Round2Result' and id in 
	(select id from sysobjects where name='SurvivorTeams' and xtype='u'))
begin
	alter table SurvivorTeams add Round2Result bit
end
go
if not exists (select * from syscolumns where name='Round3Result' and id in 
	(select id from sysobjects where name='SurvivorTeams' and xtype='u'))
begin
	alter table SurvivorTeams add Round3Result bit
end
go
if not exists (select * from syscolumns where name='Round4Result' and id in 
	(select id from sysobjects where name='SurvivorTeams' and xtype='u'))
begin
	alter table SurvivorTeams add Round4Result bit
end
go
if not exists (select * from syscolumns where name='Round5Result' and id in 
	(select id from sysobjects where name='SurvivorTeams' and xtype='u'))
begin
	alter table SurvivorTeams add Round5Result bit
end
go
if not exists (select * from syscolumns where name='Round6Result' and id in 
	(select id from sysobjects where name='SurvivorTeams' and xtype='u'))
begin
	alter table SurvivorTeams add Round6Result bit
end
go
